IF EXISTS (
	SELECT SPECIFIC_NAME 
	FROM INFORMATION_SCHEMA.ROUTINES 
	WHERE SPECIFIC_SCHEMA = N'testing'
		AND SPECIFIC_NAME = N'prc_GetQuestionGroupDetail' 
)
	BEGIN
		DROP PROCEDURE testing.prc_GetQuestionGroupDetail
		PRINT 'Dropping testing.prc_GetQuestionGroupDetail'
	END
GO

/*-------------------------------------------------------------------------------------
** Name:	prc_GetQuestionGroupDetail
** Purpose:	Get question list belong to a question group
** 
** ------------------------------------------------------------------------------------
** Input Parameters: 
** @QuestionGroupId
** N/A
** 
** ------------------------------------------------------------------------------------
** Usage
** EXEC testing.prc_GetQuestionGroupDetail 1
**
** ------------------------------------------------------------------------------------
** Author:    Hau
** Date:      2012-07-05
** Database:  SIVN_OnlineTestingEngine
**
** ------------------------------------------------------------------------------------
** Modifications:
** 2012-07-05	Hau		Create
** ----------------------------------------------------------------------------------*/

CREATE PROCEDURE [testing].[prc_GetQuestionGroupDetail]
@QuestionGroupId int
AS
BEGIN
	SET NOCOUNT ON;
	
	select qg.Id, qg.Name, qg.GroupId, qg.UserId, qg.CategoryId, qg.Time, qg.Image, 
		qg.CreatedDate, qg.CreatedBy, qg.ModifiedDate, qg.ModifiedBy,
		(select COUNT(Id) from testing.[Like] where QuestionGroupId = qg.Id and IsDislike = 'false') 'Likes',
		(select COUNT(Id) from testing.[Like] where QuestionGroupId = qg.Id and IsDislike = 'true') 'DisLikes'
	FROM testing.QuestionGroup qg
	where qg.Id = @QuestionGroupId
         
 SELECT @@Identity              
  
END

GO

PRINT 'Created [testing].[prc_GetQuestionGroupDetail]'
GO